﻿IF EXISTS (
    SELECT
      1
    FROM sysobjects
    WHERE id = OBJECT_ID('bd_proc_deleteRepair')
      AND type IN ('P', 'PC')
  )
  DROP PROCEDURE bd_proc_deleteRepair

GO

-- =============================================
-- Author:      YFC
-- Create date: 2020-07-14 21:09:15
-- Database:    scm_main_hysj20200702
-- Description: 补出勤-删除补出勤记录
-- =============================================

CREATE PROCEDURE bd_proc_deleteRepair
(
  @order_id VARCHAR(MAX),
  @userId BIGINT
)
AS
BEGIN

  CREATE TABLE #result
  (
    order_id BIGINT,
    order_no VARCHAR(30) COLLATE Chinese_PRC_CI_AS,
    error_msg VARCHAR(200) COLLATE Chinese_PRC_CI_AS
  )

  CREATE TABLE #account
  (
    order_id BIGINT,
    user_serial BIGINT,
    repair_year INT,
    repair_month INT,
    is_lock INT DEFAULT 0
  )

  CREATE TABLE #order
  (
    order_id BIGINT,
    order_no VARCHAR(30),
    bill_status INT DEFAULT 0    --1:已封账 2：在审批
  )

  INSERT INTO #order(order_id)
  SELECT value FROM STRING_SPLIT(@order_id, ',')

  --非待审批的不可以删除
  UPDATE ids
  SET bill_status = 2
     ,order_no = ra.order_no
  FROM #order ids
  INNER JOIN bd_repair_attendance ra ON ra.order_id = ids.order_id AND ra.status > 0


  --考勤已封账的不能删除
  INSERT INTO #account(order_id, user_serial, repair_year, repair_month)
  SELECT ids.order_id, jl.user_serial, YEAR(jl.sj), MONTH(jl.sj)
  FROM #order ids
  INNER JOIN lr_jl jl ON jl.order_id = ids.order_id

  UPDATE tmp
  SET is_lock = 1
  FROM #account tmp
  INNER JOIN bd_kq_accounts acc ON acc.user_serial = tmp.user_serial AND acc.year = tmp.repair_year AND acc.month = tmp.repair_month


  UPDATE o
  SET bill_status = 1
  FROM #order o
  INNER JOIN (
      SELECT
        order_id, COUNT(1) qty
      FROM #account
      WHERE is_lock > 0
      GROUP BY order_id
      HAVING COUNT(1) > 0
    ) x ON x.order_id = o.order_id


  INSERT INTO #result(order_id, order_no, error_msg)
  SELECT order_id, order_no, '补出勤单中包含已封账的员工，不能删除'
  FROM #order
  WHERE bill_status = 1

  INSERT INTO #result(order_id, order_no, error_msg)
  SELECT order_id, order_no, '补出勤单已进入审批流程，不能删除'
  FROM #order
  WHERE bill_status = 2


  IF NOT EXISTS(SELECT * FROM #result)
  BEGIN
    --标记删除
    UPDATE bd_repair_attendance
    SET is_del = 1, last_operator = @userId, last_operate_time = GETDATE()
    WHERE order_id IN (
      SELECT
        order_id
      FROM #order
    )	
  END

  SELECT * FROM #result

  DROP TABLE #account
  DROP TABLE #order
  DROP TABLE #result

END
GO